package DB;

import Model.*;

import java.sql.*;
import java.util.ArrayList;

public class DBEksemplar 
{
	private DBFormat dbFor;
	private Connection con;
	private static DBEksemplar  instance = null;
    
    public static DBEksemplar getInstance()
    {
        if (instance == null)
        {
          instance = new DBEksemplar();
        }
        return instance;
    }
	private DBEksemplar()
	{
		dbFor = DBFormat.getInstance();
		con = DBConnection.getInstance().getDBcon();
	}
	
	public ArrayList<Eksemplar> getAlleEksemplar()
	{
		return miscWhere("");
	}
	
	private Eksemplar singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		Eksemplar eks = new Eksemplar();
	    String query =  buildQuery(wClause);
		try{ 
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() ){
	 				eks = buildEksemplar(results, retrieveAssociation);
                            stmt.close();	                            
			}
		}
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return eks;
	}
	
	private ArrayList<Eksemplar> miscWhere(String wClause)
	{
		ResultSet results;
		ArrayList<Eksemplar> list = new ArrayList<Eksemplar>();	
		
		String query =  buildQuery(wClause);
  
  		try
  		{ 
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);		 		

			while( results.next() )
			{
				 Eksemplar eks = new Eksemplar();
		     	 eks = buildEksemplar(results, true);	
                 list.add(eks);	
			}
			stmt.close();

		}//slut try	
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}
	
	private String buildQuery(String wClause)
	{
	    String query="SELECT * FROM Eksemplar";
		
		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;
			
		return query;
	}
	
	private Eksemplar buildEksemplar(ResultSet results, boolean rA)
    {   
		DBEksemplar dbEks = new DBEksemplar();
		DBUdlej_Salg_Vare dbUsv = new DBUdlej_Salg_Vare();
		Eksemplar eks = new Eksemplar();
          try
          {
        	  	eks.setEksemplarId(results.getInt(1)); 
        	  	eks.setStatus(results.getString(2));
        	  	if(rA == true)
        	  	{
        	  		eks.setFormat(dbFor.findFormat(results.getInt(3), false));
            	  	eks.setUsv(dbUsv.findUdlej_Salg_VareViaEksemplar(results.getInt(1), false));
        	  	}
        	  	
        	  	
          }
         catch(Exception e)
         {
             System.out.println("error in building the eksemplar object");
         }
         return eks;
    }
	
	private void create(String wClause)
	{
	    String query="INSERT INTO Eksemplar ";
	   
		if (wClause.length()>0)
		{
			query=query+ wClause;
			
			Statement stmt;
			String sql;     
			int rows;
			
			try
			{
			stmt = con.createStatement(); 
			rows = stmt.executeUpdate(query); 
			con.commit(); 
			stmt.close();
			}
			catch (Exception e)
			{ 
			  e.printStackTrace(); 
			}
		}
		else
		{
		}
	}
	
	public void createEksemplar(Eksemplar e)
	{
			String wClause = "VALUES " 
            + "(" + e.getEksemplarId()  + ","
            + "'" + e.getStatus() + "',"
            + "'" + e.getFormat().getFormatid() + "')";
            create(wClause);
	}
	
	public void updateEksemplar(Eksemplar e)
	{
		
		PreparedStatement prepstmt;
		try 
		{     
		  prepstmt = con.prepareStatement
		   ("UPDATE Eksemplar SET" 
				+ " eksemplarid = " + e.getEksemplarId() + ","
		   		+ " status = '" + e.getStatus() + "',"
		   		+ " formatid = " + e.getFormat().getFormatid() + ""
		   		+ " WHERE eksemplarid = " + e.getEksemplarId() + "");
		  String test = "UPDATE Eksemplar SET " 
					+ " eksemplarid = " + e.getEksemplarId() + ""
			   		+ " status = '" + e.getStatus() + "'"
			   		+ " formatid = " + e.getFormat().getFormatid() + ""
			   		+ " WHERE eksemplarid = " + e.getEksemplarId() + "";
		  prepstmt.executeUpdate();
		             
		  con.commit();
		  prepstmt.close();
		}
		catch (Exception ex) 
		{
		  ex.printStackTrace();
		}
	}
	
	public void deleteEksemplar(int id)
	{
		PreparedStatement prepstmt;
		try 
		{     
		  prepstmt = con.prepareStatement
		    ("DELETE FROM Eksemplar WHERE eksemplarid = " + id + "");   
		  prepstmt.executeUpdate();          
		  con.commit();
		  prepstmt.close();
		}
		catch (Exception e) 
		{
		  e.printStackTrace();
		}
	}
	
	public Eksemplar findEksemplar(int id, boolean rA) 
    {
		String wClause = " eksemplarid = " + id + "";
    	return singleWhere(wClause, rA);
    }

	public ArrayList<Eksemplar> findEksemplarViaFormat(int formatid, boolean rA) 
	{
		String query = " SELECT * from Eksemplar where " + formatid + " = formatid";
    	ResultSet results;
		ArrayList list = new ArrayList();	
  
  		try
  		{ // read the customer from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);		 		

			while( results.next() )
			{
		     	 Eksemplar eks = new Eksemplar();
		     	 eks = buildEksemplar(results, rA);	
                 list.add(eks);	
			}         
			stmt.close();
		}
	 	catch(Exception e)
	 	{
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}
}
